/**
 * 创建角色属性点和装备系统相关表的迁移脚本
 * 根据项目已有结构，合理设计表结构或者改造已有表结构，完成装备和属性加点系统
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

class CharacterAttributeEquipmentMigration {
    constructor() {
        this.connection = null;
    }

    async connect() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    /**
     * 创建角色属性点分配表
     */
    async createCharacterAttributePointsTable() {
        console.log('\n📊 创建角色属性点分配表...');

        const sql = `
            CREATE TABLE IF NOT EXISTS \`character_attribute_points\` (
              \`id\` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
              \`characterId\` int NOT NULL COMMENT '角色ID',
              \`strength\` int NOT NULL DEFAULT '0' COMMENT '力量属性点(ap)',
              \`constitution\` int NOT NULL DEFAULT '0' COMMENT '体质属性点(hp)',
              \`intelligence\` int NOT NULL DEFAULT '0' COMMENT '智力属性点(mp)',
              \`agility\` int NOT NULL DEFAULT '0' COMMENT '敏捷属性点(sp)',
              \`availablePoints\` int NOT NULL DEFAULT '0' COMMENT '剩余可分配属性点',
              \`createdAt\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
              \`updatedAt\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
              PRIMARY KEY (\`id\`),
              UNIQUE KEY \`REL_character_attribute_points_characterId\` (\`characterId\`),
              KEY \`idx_characterId\` (\`characterId\`),
              CONSTRAINT \`FK_character_attribute_points_characterId\` FOREIGN KEY (\`characterId\`) REFERENCES \`characters\` (\`id\`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色属性点分配表';
        `;

        try {
            await this.connection.execute(sql);
            console.log('✅ 角色属性点分配表创建成功');
        } catch (error) {
            console.error('❌ 角色属性点分配表创建失败:', error.message);
            throw error;
        }
    }

    /**
     * 创建角色装备穿戴表
     */
    async createCharacterEquipmentTable() {
        console.log('\n⚔️ 创建角色装备穿戴表...');

        const sql = `
            CREATE TABLE IF NOT EXISTS \`character_equipment\` (
              \`id\` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
              \`characterId\` int NOT NULL COMMENT '角色ID',
              \`rightHand\` int DEFAULT NULL COMMENT '右手武器装备ID',
              \`body\` int DEFAULT NULL COMMENT '身体铠甲装备ID',
              \`head\` int DEFAULT NULL COMMENT '头部头盔装备ID',
              \`neck\` int DEFAULT NULL COMMENT '颈部项链装备ID',
              \`leftHand\` int DEFAULT NULL COMMENT '左手护腕装备ID',
              \`foot\` int DEFAULT NULL COMMENT '脚部战靴装备ID',
              \`createdAt\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
              \`updatedAt\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
              PRIMARY KEY (\`id\`),
              UNIQUE KEY \`REL_character_equipment_characterId\` (\`characterId\`),
              KEY \`idx_characterId\` (\`characterId\`),
              KEY \`idx_rightHand\` (\`rightHand\`),
              KEY \`idx_body\` (\`body\`),
              KEY \`idx_head\` (\`head\`),
              KEY \`idx_neck\` (\`neck\`),
              KEY \`idx_leftHand\` (\`leftHand\`),
              KEY \`idx_foot\` (\`foot\`),
              CONSTRAINT \`FK_character_equipment_characterId\` FOREIGN KEY (\`characterId\`) REFERENCES \`characters\` (\`id\`) ON DELETE CASCADE,
              CONSTRAINT \`FK_character_equipment_rightHand\` FOREIGN KEY (\`rightHand\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL,
              CONSTRAINT \`FK_character_equipment_body\` FOREIGN KEY (\`body\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL,
              CONSTRAINT \`FK_character_equipment_head\` FOREIGN KEY (\`head\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL,
              CONSTRAINT \`FK_character_equipment_neck\` FOREIGN KEY (\`neck\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL,
              CONSTRAINT \`FK_character_equipment_leftHand\` FOREIGN KEY (\`leftHand\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL,
              CONSTRAINT \`FK_character_equipment_foot\` FOREIGN KEY (\`foot\`) REFERENCES \`character_equipment_inventory\` (\`id\`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色装备穿戴表';
        `;

        try {
            await this.connection.execute(sql);
            console.log('✅ 角色装备穿戴表创建成功');
        } catch (error) {
            console.error('❌ 角色装备穿戴表创建失败:', error.message);
            throw error;
        }
    }

    /**
     * 为现有角色初始化属性点记录
     */
    async initializeAttributePointsForExistingCharacters() {
        console.log('\n🔄 为现有角色初始化属性点记录...');

        try {
            // 查询所有现有角色
            const [characters] = await this.connection.execute(
                'SELECT id, lvl FROM characters WHERE id >= 2001'
            );

            console.log(`📋 找到 ${characters.length} 个现有角色`);

            for (const character of characters) {
                // 计算该角色应该有的属性点（每级4点）
                const totalPoints = (character.lvl - 1) * 4;

                // 检查是否已有属性点记录
                const [existing] = await this.connection.execute(
                    'SELECT id FROM character_attribute_points WHERE characterId = ?',
                    [character.id]
                );

                if (existing.length === 0) {
                    // 创建属性点记录
                    await this.connection.execute(
                        `INSERT INTO character_attribute_points 
                         (characterId, strength, constitution, intelligence, agility, availablePoints) 
                         VALUES (?, 0, 0, 0, 0, ?)`,
                        [character.id, totalPoints]
                    );
                    console.log(`✅ 角色 ${character.id} 属性点记录已创建，可用点数: ${totalPoints}`);
                } else {
                    console.log(`⏭️ 角色 ${character.id} 属性点记录已存在，跳过`);
                }
            }

            console.log('✅ 现有角色属性点记录初始化完成');
        } catch (error) {
            console.error('❌ 初始化属性点记录失败:', error.message);
            throw error;
        }
    }

    /**
     * 为现有角色初始化装备记录
     */
    async initializeEquipmentForExistingCharacters() {
        console.log('\n🛡️ 为现有角色初始化装备记录...');

        try {
            // 查询所有现有角色
            const [characters] = await this.connection.execute(
                'SELECT id FROM characters WHERE id >= 2001'
            );

            console.log(`📋 找到 ${characters.length} 个现有角色`);

            for (const character of characters) {
                // 检查是否已有装备记录
                const [existing] = await this.connection.execute(
                    'SELECT id FROM character_equipment WHERE characterId = ?',
                    [character.id]
                );

                if (existing.length === 0) {
                    // 创建装备记录
                    await this.connection.execute(
                        `INSERT INTO character_equipment 
                         (characterId, rightHand, body, head, neck, leftHand, foot) 
                         VALUES (?, NULL, NULL, NULL, NULL, NULL, NULL)`,
                        [character.id]
                    );
                    console.log(`✅ 角色 ${character.id} 装备记录已创建`);
                } else {
                    console.log(`⏭️ 角色 ${character.id} 装备记录已存在，跳过`);
                }
            }

            console.log('✅ 现有角色装备记录初始化完成');
        } catch (error) {
            console.error('❌ 初始化装备记录失败:', error.message);
            throw error;
        }
    }

    /**
     * 验证表结构
     */
    async validateTables() {
        console.log('\n🔍 验证表结构...');

        try {
            // 验证角色属性点表
            const [attrPointsTable] = await this.connection.execute(
                "SHOW TABLES LIKE 'character_attribute_points'"
            );
            if (attrPointsTable.length > 0) {
                console.log('✅ character_attribute_points 表存在');

                const [attrPointsColumns] = await this.connection.execute(
                    'DESCRIBE character_attribute_points'
                );
                console.log(`📊 属性点表列数: ${attrPointsColumns.length}`);
            } else {
                console.log('❌ character_attribute_points 表不存在');
            }

            // 验证角色装备表
            const [equipmentTable] = await this.connection.execute(
                "SHOW TABLES LIKE 'character_equipment'"
            );
            if (equipmentTable.length > 0) {
                console.log('✅ character_equipment 表存在');

                const [equipmentColumns] = await this.connection.execute(
                    'DESCRIBE character_equipment'
                );
                console.log(`⚔️ 装备表列数: ${equipmentColumns.length}`);
            } else {
                console.log('❌ character_equipment 表不存在');
            }

            console.log('✅ 表结构验证完成');
        } catch (error) {
            console.error('❌ 表结构验证失败:', error.message);
            throw error;
        }
    }

    /**
     * 执行完整的迁移流程
     */
    async runMigration() {
        console.log('🚀 开始角色属性点和装备系统迁移...\n');

        try {
            await this.connect();

            // 创建表
            await this.createCharacterAttributePointsTable();
            await this.createCharacterEquipmentTable();

            // 初始化现有角色数据
            await this.initializeAttributePointsForExistingCharacters();
            await this.initializeEquipmentForExistingCharacters();

            // 验证表结构
            await this.validateTables();

            console.log('\n🎉 角色属性点和装备系统迁移完成！');
            console.log('\n📋 迁移总结:');
            console.log('  ✅ 创建了 character_attribute_points 表');
            console.log('  ✅ 创建了 character_equipment 表');
            console.log('  ✅ 为现有角色初始化了属性点记录');
            console.log('  ✅ 为现有角色初始化了装备记录');
            console.log('\n🎮 现在可以使用以下WebSocket指令:');
            console.log('  📊 1005: 属性点分配');
            console.log('  ⚔️ 1137: 装备穿戴');
            console.log('  🛡️ 1136: 装备卸下');
            console.log('  📋 1130: 查询装备信息');

        } catch (error) {
            console.error('\n💥 迁移过程中发生错误:', error.message);
            throw error;
        } finally {
            await this.disconnect();
        }
    }
}

// 主函数
async function main() {
    const migration = new CharacterAttributeEquipmentMigration();

    try {
        await migration.runMigration();
        process.exit(0);
    } catch (error) {
        console.error('💥 迁移失败:', error.message);
        process.exit(1);
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main();
}

module.exports = CharacterAttributeEquipmentMigration;
